package com.hdwx.weike.dao;

import com.hdwx.weike.Utils.JdbcUtils;
import com.hdwx.weike.beans.PageBean;
import com.hdwx.weike.beans.VideoBean;
import com.hdwx.weike.beans.VideoSection;
import com.hdwx.weike.beans.VideoType;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * Created by hdwx on 2016/12/24.
 */
//进行video的crud操作
public class VideoDao extends BaseDao{
    //通过分段、分类、（最新|评分|浏览量|收藏量）、页码查询该页的video数据
    public PageBean queryByPageIndex(int index,int sectionId,int typeId,int sortId){
        //创建一个pageBean对象,存放相关数据，返回给前端
        PageBean pageBean=new PageBean();
        pageBean.setCurrentPage(index);
        pageBean.setTypeId(typeId);
        pageBean.setSectionId(sectionId);
        pageBean.setSortId(sortId);
        String table="t_video";
        String condition=null;
        //判断分段id和分类id是否为0,0是全部
        if(sectionId!=0&&typeId!=0){
            condition="v_section_id="+sectionId+" and v_type_id="+typeId;
        }else if(sectionId!=0&&typeId==0){
            condition="v_section_id="+sectionId;
        }else if(sectionId==0&&typeId!=0){
            condition="v_type_id="+typeId;
        }

        switch (sortId){
            case 0://默认最新排序
                if(condition!=null){
                    condition=condition+" order by insert_time desc";
                }else{
                    table=table+" order by insert_time desc";
                }
                break;
            case 1://评分排序
                if(condition!=null){
                    condition=condition+" order by v_score desc";
                }else{
                    table=table+" order by v_score desc";
                }
                break;
            case 2://浏览量排序
                if(condition!=null){
                    condition=condition+" order by v_view_count desc";
                }else{
                    table=table+" order by v_view_count desc";
                }
                break;
            case 3://收藏量排序
                if(condition!=null){
                    condition=condition+" order by v_collections desc";
                }else{
                    table=table+" order by v_collections desc";
                }
                break;
        }
        int count=0;//数据库中的video总数
        int totalPage=0;//video可以分成的总页数
        //1.查询出所有video的总数

        String[] col={"count(*)"};
        ResultSet set=null;
        try {
            System.out.println(table);
            System.out.println(condition);
            //查询所有video数量，返回一个结果集
            set=query(table,col,condition,null);
            set.next();//移到结果集的第一行
            count = set.getInt(1);//取第一列的数据；
            System.out.println("video总数:"+count);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(set);
        }
        //2.计算出总页数,并赋值给pageBean
        totalPage=(int)Math.ceil((double)count/pageBean.getPageNum());
        pageBean.setTotalPage(totalPage);
        System.out.println("总页数："+totalPage);
        //3.判断当前要查询的页数是否大于总页数，若大于，说明没有数据，不进行查找
        if(pageBean.getCurrentPage()>totalPage){
            return pageBean;
        }else {
            //4.要查询的页数小于总页数，进行查找,sql语句的limit
            String[] col1={"*"};
            int[] limit={pageBean.getPageNum()*(pageBean.getCurrentPage()-1),pageBean.getPageNum()};
            ResultSet set2=null;
            //创建一个集合，用来装查询到的video数据
            List<VideoBean> data=new ArrayList<VideoBean>();
            try {
                set2=query(table,col1,condition,limit);
                while(set2.next()){
                    VideoBean videoBean = new VideoBean();
                    videoBean.setId(set2.getInt("v_id"));//视频id
                    videoBean.setName(set2.getString("v_name"));//视频名字
                    videoBean.setVideoUrl(set2.getString("v_url"));//视频地址
                    videoBean.setVideoCoverImg(set2.getString("v_cover_img"));//视频封面图片
                    videoBean.setUploadUsername(set2.getString("v_upload_user"));//上传者
                    videoBean.setUploadUserFirm(set2.getString("v_upload_firm"));//上传用户公司
                    videoBean.setScore(set2.getDouble("v_score"));//视频评分
                    videoBean.setCommentCount(set2.getInt("v_comment_count"));//视频评论数量
                    videoBean.setCollections(set2.getInt("v_collections"));//视频收藏量
                    videoBean.setViewCount(set2.getInt("v_view_count"));//视频浏览量
                    videoBean.setTypeId(set2.getInt("v_type_id"));//视频所属分类id
                    videoBean.setSectionId(set2.getInt("v_section_id")); //视频所属分段id
                    data.add(videoBean);
                }
                //将查询到的video数据的集合赋值给pageBean对象
                pageBean.setData(data);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JdbcUtils.close(set2);
            }
        }
        return pageBean;
    }
    //查询所有的学段，返回一个集合
    public List<VideoSection> queryAllSections(){
        List<VideoSection> sectionsList = new ArrayList<VideoSection>();
        String table="t_video_section order by section_id asc";
        String[] col={"*"};
        ResultSet set=null;
        try {
            set=query(table,col,null,null);//查询所有的学段
            while (set.next()){
                VideoSection videoSection = new VideoSection();
                videoSection.setId(set.getInt("section_id"));
                videoSection.setName(set.getString("section_name"));
                sectionsList.add(videoSection);//将每一个学段对象存入集合
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(set);
        }
        return sectionsList;
    }
    //查询所有的分类，返回一个集合
    public List<VideoType> queryAllTypes(){
        List<VideoType> typeList = new ArrayList<VideoType>();
        String table="t_video_type order by type_id asc";
        String[] col={"*"};
        ResultSet set=null;
        try {
            set=query(table,col,null,null);//查询所有的学段
            while (set.next()){
                VideoType videoType = new VideoType();
                videoType.setId(set.getInt("type_id"));
                videoType.setName(set.getString("type_name"));
                typeList.add(videoType);//将每一个学段对象存入集合
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(set);
        }
        return typeList;
    }

    //通过关键字查找页面视频,可排序，分页
    public PageBean queryByPageName(String name,int index,int sortId){
        //创建一个pageBean对象,存放相关数据，返回给前端
        PageBean pageBean=new PageBean();
        pageBean.setCurrentPage(index);
        pageBean.setSortId(sortId);
        String table="t_video";
        String condition="v_name like '%"+name+"%'";
        switch (sortId){
            case 0://默认最新排序
                    condition=condition+" order by insert_time desc";
                break;
            case 1://评分排序
                    condition=condition+" order by v_score desc";
                break;
            case 2://浏览量排序
                    condition=condition+" order by v_view_count desc";
                break;
            case 3://收藏量排序
                    condition=condition+" order by v_collections desc";
                break;
        }
        int count=0;//数据库中的video总数
        int totalPage=0;//video可以分成的总页数
        //1.查询出所有video的总数
        String[] col={"count(*)"};
        ResultSet set=null;
        try {
            System.out.println(table);
            System.out.println(condition);
            //查询所有video数量，返回一个结果集
            set=query(table,col,condition,null);
            set.next();//移到结果集的第一行
            count = set.getInt(1);//取第一列的数据；
            System.out.println("video总数:"+count);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(set);
        }
        //2.计算出总页数,并赋值给pageBean
        totalPage=(int)Math.ceil((double)count/pageBean.getPageNum());
        pageBean.setTotalPage(totalPage);
        System.out.println("总页数："+totalPage);
        //3.判断当前要查询的页数是否大于总页数，若大于，说明没有数据，不进行查找
        if(pageBean.getCurrentPage()>totalPage){
            return pageBean;
        }else {
            //4.要查询的页数小于总页数，进行查找,sql语句的limit
            String[] col1={"*"};
            int[] limit={pageBean.getPageNum()*(pageBean.getCurrentPage()-1),pageBean.getPageNum()};
            ResultSet set2=null;
            //创建一个集合，用来装查询到的video数据
            List<VideoBean> data=new ArrayList<VideoBean>();
            try {
                set2=query(table,col1,condition,limit);
                while(set2.next()){
                    VideoBean videoBean = new VideoBean();
                    videoBean.setId(set2.getInt("v_id"));//视频id
                    videoBean.setName(set2.getString("v_name"));//视频名字
                    videoBean.setVideoUrl(set2.getString("v_url"));//视频地址
                    videoBean.setVideoCoverImg(set2.getString("v_cover_img"));//视频封面图片
                    videoBean.setUploadUsername(set2.getString("v_upload_user"));//上传者
                    videoBean.setUploadUserFirm(set2.getString("v_upload_firm"));//上传用户公司
                    videoBean.setScore(set2.getDouble("v_score"));//视频评分
                    videoBean.setCommentCount(set2.getInt("v_comment_count"));//视频评论数量
                    videoBean.setCollections(set2.getInt("v_collections"));//视频收藏量
                    videoBean.setViewCount(set2.getInt("v_view_count"));//视频浏览量
                    videoBean.setTypeId(set2.getInt("v_type_id"));//视频所属分类id
                    videoBean.setSectionId(set2.getInt("v_section_id")); //视频所属分段id
                    data.add(videoBean);
                }
                //将查询到的video数据的集合赋值给pageBean对象
                pageBean.setData(data);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JdbcUtils.close(set2);
            }
        }
        return pageBean;
    }

    //按给定顺序降序，查询前n个
    public List<VideoBean> queryByOrder(String orderBy,int count){
        //创建一个集合存放查询到的数据
        List<VideoBean> list=new ArrayList<VideoBean>();
        String table="t_video order by "+orderBy;
        String[] col={"*"};
        int[] limit={0,count};
        ResultSet set=null;
        try {
            set=query(table,col,null,limit);
            while (set.next()){
                VideoBean videoBean = new VideoBean();
                videoBean.setId(set.getInt("v_id"));//视频id
                videoBean.setName(set.getString("v_name"));//视频名字
                videoBean.setVideoUrl(set.getString("v_url"));//视频地址
                videoBean.setVideoCoverImg(set.getString("v_cover_img"));//视频封面图片
                videoBean.setUploadUsername(set.getString("v_upload_user"));//上传者
                videoBean.setUploadUserFirm(set.getString("v_upload_firm"));//上传用户公司
                videoBean.setScore(set.getDouble("v_score"));//视频评分
                videoBean.setCommentCount(set.getInt("v_comment_count"));//视频评论数量
                videoBean.setCollections(set.getInt("v_collections"));//视频收藏量
                videoBean.setViewCount(set.getInt("v_view_count"));//视频浏览量
                videoBean.setTypeId(set.getInt("v_type_id"));//视频所属分类id
                videoBean.setSectionId(set.getInt("v_section_id")); //视频所属分段id
                list.add(videoBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(set);
        }
        return list;
    }

    //通过id查找视频的浏览量
    public int queryCollectionsById(int id){
        if(id!=0){
            String table="t_video";
            String[] col={"v_view_count"};
            String condition="v_id="+id;
            ResultSet set=null;
            try {
                set=query(table,col,condition,null);
                if(set.next()){
                    return set.getInt("v_view_count");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JdbcUtils.close(set);
            }
        }
        return -1;
    }
    //通过id更新视频浏览量
    public boolean updateById(int id,Object[] value){
        String table="t_video";
        String[] col={"v_view_count"};
        String condition="v_id="+id;
        return change(table,col,value,condition);
    }



    public static void main(String[] args) {
//        PageBean pageBean=new VideoDao().queryByPageIndex(1,0,0,0);
//        PageBean pageBean=new VideoDao().queryByPageIndex(1,7,3);
//        PageBean pageBean = new VideoDao().queryBySectionId(7,3,1);
//        System.out.println(pageBean.getData());
//        List<VideoType> sections = new VideoDao().queryAllTypes();
//        System.out.println(sections.size());
//        PageBean pageBean=new VideoDao().queryByPageName("会计",1,0);
//        List<VideoBean> list = new VideoDao().queryByCollection(5);
//        for (VideoBean videoBean : list) {
//            System.out.println(videoBean.getName());
//        }
        boolean b = new VideoDao().updateById(1, new Object[]{133});
        System.out.println(b);
    }
}
